package controllers;

import java.io.File;
import java.io.IOException;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.CellView;
import system.Controller;
import models.Kategori;


import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import models.Pesanan;

/**
 * Laporan Controller
 * 
 * @author Rezan Achmad
 */
@WebServlet(name = "LaporanController", urlPatterns = {"/laporan", "/laporan/", "/laporan/index"})
public class LaporanController extends Controller {
    //  Constants
    private static final int _SEMUA = 0;
    private static final int _BELUM_DIBAYAR = 1;
    private static final int _SUDAH_KONFIRMASI = 2;
    private static final int _SUDAH_DIBAYAR = 3;
    private static final int _BARANG_SUDAH_TIBA = 4;
    private static final int[] _ID_PESANAN = new int[]{
        _SEMUA,
        _BELUM_DIBAYAR,
        _SUDAH_KONFIRMASI,
        _SUDAH_DIBAYAR,
        _BARANG_SUDAH_TIBA
    };
    private static final String _sSEMUA = "Semua";
    private static final String _sBELUM_DIBAYAR = "Belum dibayar";
    private static final String _sSUDAH_KONFIRMASI = "Sudah konfirmasi";
    private static final String _sSUDAH_DIBAYAR = "Sudah dibayar";
    private static final String _sBARANG_SUDAH_TIBA = "Sudah tiba";
    private static final String[] _sPESANAN = new String[]{
        _sSEMUA,
        _sBELUM_DIBAYAR,
        _sSUDAH_KONFIRMASI,
        _sSUDAH_DIBAYAR,
        _sBARANG_SUDAH_TIBA
    };

    @Override
    protected void initRules() {
        rules.put("/laporan", identity.isManager() || identity.isAdmin());
        rules.put("/laporan/", identity.isManager() || identity.isAdmin());
        rules.put("/laporan/index", identity.isManager() || identity.isAdmin());
    }

    @Override
    protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String userPath = request.getServletPath();

        if (userPath.equals("/laporan") || userPath.equals("/laporan/") || userPath.equals("/laporan/index")) {
            actionIndex(request, response);
        } else {
        }
    }

    private void actionIndex(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //String filePath = request.getServletContext().getRealPath("/upload") + "\\Laporan Penjualan Barang.xls";
        String filePath = request.getServletContext().getRealPath("/") + "upload/" + "Laporan Penjualan Barang.xls";
        String downloadPath = request.getServletContext() + "\\Laporan Penjualan Barang.xls";

        //  get attributes
        request.setAttribute("listStatusPesanan", _sPESANAN);
        request.setAttribute("listIDStatusPesanan", _ID_PESANAN);
        request.setAttribute("listKategori", Kategori.getAll());

        String startdate = null;
        String enddate = null;
        int kategoriBarang = 0;
        int statusPesanan = 0;

        if (isPost) {
            String categoryName;
            String status;
            // SQL Query here
            startdate = request.getParameter("start_date");
            enddate = request.getParameter("end_date");
            kategoriBarang = Integer.parseInt(request.getParameter("kategoriBarang"));
            statusPesanan = Integer.parseInt(request.getParameter("statusPesanan"));
            status = _sPESANAN[statusPesanan];
            String sqlQuery = "SELECT * "
                    + "FROM `pesanan_item`,`pesanan`,`barang` "
                    + "WHERE (`pesanan_item`.`pesanan_id` = `pesanan`.`id`) "
                    + "AND (`pesanan_item`.`barang_id` = `barang`.`id`) "
                    + "AND (`pesanan`.`tanggal` >= '" + startdate + "') "
                    + "AND (`pesanan`.`tanggal` <= '" + enddate + " 23:59:59') ";

            if (statusPesanan == 0) {
                //  All status retrieved
            } else {
                sqlQuery += "AND (`pesanan`.`status` = " + statusPesanan + ") ";
            }
            if (kategoriBarang == 0) {
                //  All categories retrieved
                categoryName = "Semua";
            } else {
                sqlQuery += "AND (`barang`.`kategori_id` = " + kategoriBarang + ")";
                categoryName = Kategori.get(kategoriBarang).nama;
            }

            Map result = Pesanan.getBySql(sqlQuery);

            try {
                if (generateReport(result, categoryName, status, startdate, enddate, filePath) == true) {
                    request.setAttribute("pathLaporan", filePath);
                } else{
                    request.setAttribute("pathLaporan", null);
                }

            } catch (Exception ex) {
                System.out.println("exception" + ex.toString());
                throw new ServletException("Exception in Excel Sample Servlet", ex);
            } finally {
            }
        }

        request.setAttribute("chosen_categ", kategoriBarang);
        request.setAttribute("chosen_status", statusPesanan);
        request.setAttribute("startdate", startdate);
        request.setAttribute("enddate", enddate);

        setView("/laporan/index", request, response);
    }

    private boolean generateReport(Map<String, Integer> map, String kategori, String status, String startDate, String endDate, String sFilePath) {
        try {
            //  init workbook
            WritableWorkbook workbook = Workbook.createWorkbook(new File(sFilePath));
            WritableSheet sheet = workbook.createSheet("Laporan", 0);

            //  formating title
            sheet.mergeCells(0, 0, 2, 0);
            WritableFont wf = new WritableFont(WritableFont.ARIAL, 20, WritableFont.BOLD);
            WritableCellFormat titleFormat = new WritableCellFormat(wf);
            titleFormat.setAlignment(jxl.format.Alignment.CENTRE);

            //  formatting table header
            WritableFont wfTableHeader = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD);
            wfTableHeader.setColour(jxl.format.Colour.WHITE);
            WritableCellFormat headerTableFormat = new WritableCellFormat(wfTableHeader);
            headerTableFormat.setAlignment(jxl.format.Alignment.CENTRE);
            headerTableFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THICK);
            headerTableFormat.setBackground(jxl.format.Colour.BLUE);

            //  formatting content
            WritableFont wfContent = new WritableFont(WritableFont.ARIAL, 10);

            //  formating table content ganjil
            WritableCellFormat contentFormatOdd = new WritableCellFormat(wfContent);
            contentFormatOdd.setBackground(jxl.format.Colour.AQUA);
            contentFormatOdd.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.MEDIUM);

            //  formating table content genap
            WritableCellFormat contentFormatEven = new WritableCellFormat(wfContent);
            contentFormatEven.setBackground(jxl.format.Colour.WHITE);
            contentFormatEven.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.MEDIUM);

            //  write title laporan
            Label labelTitle = new Label(0, 0, "Laporan Penjualan Barang", titleFormat);
            sheet.addCell(labelTitle);

            //  report information
            Label labelKategori = new Label(0, 1, "Kategori : " + kategori);
            Label labelStatus = new Label(0, 2, "Status Pengiriman : " + status);
            Label labelStartDate = new Label(0, 3, "Tanggal Mulai : " + startDate);
            Label labelEndDate = new Label(0, 4, "Tanggal Selesai: " + endDate);

            sheet.addCell(labelKategori);
            sheet.addCell(labelStartDate);
            sheet.addCell(labelEndDate);
            sheet.addCell(labelStatus);

            //  write header table
            Label labelNo = new Label(0, 5, "No", headerTableFormat);
            sheet.addCell(labelNo);
            Label labelNamaBarang = new Label(1, 5, "Nama Barang", headerTableFormat);
            sheet.addCell(labelNamaBarang);
            Label labelJumlahBarang = new Label(2, 5, "Jumlah", headerTableFormat);
            sheet.addCell(labelJumlahBarang);

            if (map != null) {
                int i = 1;
                for (Map.Entry<String, Integer> entry : map.entrySet()) {
                    if (i % 2 == 0) {
                        labelNo = new Label(0, i + 5, Integer.toString(i), contentFormatEven);
                        labelNamaBarang = new Label(1, i + 5, entry.getKey(), contentFormatEven);
                        labelJumlahBarang = new Label(2, i + 5, Integer.toString(entry.getValue()), contentFormatEven);
                    } else {
                        labelNo = new Label(0, i + 5, Integer.toString(i), contentFormatOdd);
                        labelNamaBarang = new Label(1, i + 5, entry.getKey(), contentFormatOdd);
                        labelJumlahBarang = new Label(2, i + 5, Integer.toString(entry.getValue()), contentFormatOdd);
                    }

                    sheet.addCell(labelNo);
                    sheet.addCell(labelNamaBarang);
                    sheet.addCell(labelJumlahBarang);
                    ++i;
                }
            } else {
                labelNo = new Label(0, 6, "Data kosong", contentFormatEven);
                sheet.mergeCells(0, 6, 2, 6);
                sheet.addCell(labelNo);
            }

            CellView cellView;
            cellView = sheet.getColumnView(0);
            cellView.setSize(2000);
            sheet.setColumnView(0, cellView);

            cellView = sheet.getColumnView(1);
            cellView.setSize(7000);
            sheet.setColumnView(1, cellView);

            cellView = sheet.getColumnView(2);
            cellView.setSize(7000);
            sheet.setColumnView(2, cellView);

            workbook.write();
            workbook.close();
            return true;
        } catch (Exception e) {
            System.out.println("Exception generate : " + e.toString());
            return false;
        }
    }
}
